/***************************************************************************
 * 文件名：SqlDBContext
 * 功能：
 * 说明：
 * -------------------------------------------------------------------------
 * 创建时间：2025/7/15 16:55:31
 * 创建人：葛浩
 * 邮箱： 2094733071@qq.com
 * =========================================================================
 *
 * 修改人：   
 * 修改时间：   
 * 修改说明：   
 ***************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeGenerator
{
	/// <summary>
	/// 数据库连接基类
	/// </summary>
	public class SqlDBContext : IDBContext, IDisposable
	{
		private SqlCommand _command;
		private SqlConnection _connection;
		private SqlDataReader _reader;

		/// <summary>
		/// 构造
		/// </summary>
		/// <param name="connectionString"></param>
		public SqlDBContext(string connectionString)
		{
			if (string.IsNullOrEmpty(connectionString))
			{
				return;
			}

			_connection = new SqlConnection(connectionString);

			//检测数据库连接是否已开启
			if (_connection?.State != ConnectionState.Open)
			{
				_connection.Open();
			}

		}

		/// <summary>
		/// 获取表中字段
		/// </summary>
		/// <param name="tableName"></param>
		/// <returns></returns>
		/// <exception cref="NotImplementedException"></exception>
		public List<ColumnInfoModel> GetTableColumns(string tableName)
		{
			List<ColumnInfoModel> tables = new List<ColumnInfoModel>();

			if (_connection?.State != ConnectionState.Open)
			{
				return tables;
			}

			string sqlStr = string.Format(@"SELECT col.name AS 字段名称,
                                                TYPE_NAME(col.system_type_id) AS 字段类型,
                                                ep.value AS 字段描述
                                            FROM 
                                                sys.tables AS tab
                                            INNER JOIN 
                                                sys.columns AS col ON tab.object_id = col.object_id
                                            LEFT JOIN 
                                                sys.extended_properties AS ep ON col.object_id = ep.major_id 
                                                                              AND col.column_id = ep.minor_id 
                                                                              AND ep.class = 1
                                            WHERE 
                                                tab.name = '{0}'
                                                AND SCHEMA_NAME(tab.schema_id) = 'dbo';", tableName);

			try
			{
				_command = new SqlCommand(sqlStr, _connection);
				_reader = _command.ExecuteReader();
				//根据sql 语句获得SqlDataReader

				//循环读取数据
				while (_reader.Read())
				{
					ColumnInfoModel colom = new ColumnInfoModel();// 实例化Chapter

					colom.FieldName = _reader[0] == null ? string.Empty : _reader[0].ToString();
					colom.Type = _reader[1] == null ? string.Empty : GetFieldType(Convert.ToString(_reader[1]));
					colom.FieldText = _reader[2] == null ? string.Empty : _reader[2].ToString();
					colom.TableName = tableName;
					tables.Add(colom);
				}
			}
			catch (Exception ex)
			{
				_reader.Dispose();
				_reader.Close();
			}
			finally
			{
				_reader.Dispose();
				_reader.Close();
			}

			return tables;
		}

		/// <summary>
		/// 获取表名
		/// </summary>
		/// <returns></returns>
		/// <exception cref="NotImplementedException"></exception>
		public List<TableModel> GetTableNames()
		{
			List<TableModel> tables = new List<TableModel>();
			if (_connection?.State != ConnectionState.Open)
			{
				return tables;
			}

			string query = "SELECT t.name AS TableName, ep.value AS Description FROM sys.tables t JOIN sys.extended_properties ep ON ep.major_id = t.object_id WHERE ep.name = 'MS_Description' and ep.minor_id = '0';";
			_command = new SqlCommand(query, _connection);
			_reader = _command.ExecuteReader();

			while (_reader.Read())
			{
				string tableName = _reader.GetString(0);
				string description = _reader.GetString(1);
				tables.Add(new TableModel()
				{
					TableName = tableName,
					TableDText = description,
				});
			}

			return tables;
		}

		/// <summary>
		/// 释放资源
		/// </summary>
		/// <exception cref="NotImplementedException"></exception>
		public void Dispose()
		{
			_reader?.Close();
			_command?.Dispose();
			_connection?.Dispose();
		}

		/// <summary>
		/// 获取字段类型
		/// </summary>
		/// <param name="type"></param>
		/// <returns></returns>
		private string GetFieldType(string type)
		{
			switch (type)
			{
				case "char":
				case "varchar":
				case "nchar":
				case "nvarchar":
					type = "string";
					break;
				case "datetime":
				case "smalldatetime":
					type = "DateTime";
					break;
				case "decimal":
				case "numeric":
				case "money":
				case "smallmoney":
					type = "double";
					break;
				case "float":
					type = "float";
					break;
				case "int":
				case "smallint":
				case "tinyint":
				case "bit":
					type = "int";
					break;
				default:
					break;
			}

			return type;
		}

		/// <summary>
		/// 获取除系统数据库的所有数据库
		/// </summary>
		/// <returns></returns>
		public List<string> GetAllDataBase()
		{
			List<string> dataBases = new List<string>();
			if (_connection?.State != ConnectionState.Open)
			{
				return dataBases;
			}

			// 获取数据库架构信息
			DataTable databasesTable = _connection.GetSchema("Databases");

			foreach (DataRow row in databasesTable.Rows)
			{
				string dbName = row["database_name"].ToString();
				int dbId = Convert.ToInt32(row["dbid"]);

				// 排除系统数据库
				if (dbId > 4)
				{
					dataBases.Add(dbName);
				}
			}
			return dataBases;
		}
	}
}
